<?php
/**
 * PaperClass 
 *
 * This class is designed to work with paper.
 *
 * @author Victor Magarlamov <victor.magarlamov@gmail.com>
 * @version 1.0
 * @package presstrack
 */
class PaperClass
{
	public $_explicitType = "presstrack.PaperClass"; 

	public $iPaperId;
	public $mLabel;
	public $iThick;
	public $mSize;
	public $mCost;
	public $iTotal;
	public $iNumOfReq;
	public $iNumOfDel;
	public $_arDelivery;
	public $_arIssue;	
	
	function __construct() 
	{		
		$this->iPaperId = -1;
    }
	
	/**
	 * ReadFromDb
	 *	 
     * set user's data from database
	 *
	 * @param	int	$iduser	user ID
     */
	function ReadFromDb($idpaper)
	{
		$sql = "SELECT * FROM press_track_new.paper WHERE idpaper = $idpaper";
		$query = mysql_unbuffered_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		$res 				= mysql_fetch_object($query);
		$this->iPaperId		= $idpaper;
		$this->mLabel		= $res->label;
		$this->iThick		= $res->thick;
		$this->mSize		= $res->size;
		$this->mCost		= $res->cost;
		$this->iTotal		= $res->total;
		
		// delivery
		$sql = "SELECT * FROM press_track_new.delivery "
			 . "WHERE paper = $this->iPaperId";
		$query = mysql_unbuffered_query($sql);	
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		while ($row = mysql_fetch_object($query))
		{
			$this->_arDelivery[] = $row;
		}
		
		// issue
		$sql = "SELECT * FROM press_track_new.issue "
			 . "WHERE press_track_new.issue.paper = $this->iPaperId";
				
		$query = mysql_unbuffered_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		while ($row = mysql_fetch_object($query)) 
		{
			if ($row->request != null) 
			{
				$sql = "SELECT name as customer FROM press_track_new.user "
					 . "WHERE iduser = (SELECT press_track_new.order.customer FROM press_track_new.order " 
					 . "WHERE idorder = (SELECT press_track_new.task.order as idorder FROM press_track_new.task "
					 . "WHERE press_track_new.task.idtask = $row->request))";
				$query_cust	= mysql_unbuffered_query($sql);
				$customer	= mysql_fetch_object($query_cust);
				$row->customer = $customer->customer;
			}
			$this->_arIssue[] = $row;
		}
	}

	function SearchInDb($label, $thick, $size)
	{
		$sql = sprintf("SELECT idpaper FROM paper "
			 . "WHERE paper.label = '%s' AND paper.thick = %d AND paper.size = '%s'", 
			 $label, $thick, $size);
		$query = mysql_query($sql);
		if (mysql_num_rows($query) > 0)
		{
			$res = mysql_fetch_object($query);
			$this->ReadFromDb($res->idpaper);
		}
	}	
	
	function WriteToDb($label, $thick, $size)
	{
		$sql = sprintf("INSERT INTO press_track_new.paper "
			 . "(paper.label, paper.thick, paper.size, paper.cost, paper.total) "
			 . "VALUES ('%s', %d, '%s', %d, %d)", $label, $thick, $size, 0, 0);
		$query 		= mysql_query($sql);
		$idpaper 	= mysql_insert_id();
		$this->ReadFromDb($idpaper);
	}
	
	function AddDelivery($invoice, $creation, $paper_num, $supplier, $payer, $sum)
	{
		$result->status = false;
		
		$sql = sprintf("INSERT INTO press_track_new.delivery "
			 . "(delivery.invoice, delivery.creation, delivery.num, " 
			 . "delivery.supplier, delivery.payer, delivery.sum, delivery.paper) "
			 . "VALUES ('%s', '%s', %d, '%s', '%s', %d, %d)", 
			 $invoice, $creation, $paper_num, $supplier, $payer, $sum, $this->iPaperId);
		$query = mysql_query($sql);
		if (mysql_errno())
		{			
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		else
		{
			$result->iddelivery = mysql_insert_id();
		}
		
		$sql = "UPDATE press_track_new.paper "
			 . "SET press_track_new.paper.total = press_track_new.paper.total + $paper_num " 
			 . "WHERE press_track_new.paper.idpaper = $this->iPaperId LIMIT 1";
		$query = mysql_query($sql);
		if (mysql_errno())
		{			
			$result->message = mysql_errno() . ": " . mysql_error();			
		}
		
		$sql = "SELECT total FROM press_track_new.paper " 
			 . "WHERE press_track_new.paper.idpaper = $this->iPaperId";
		$query = mysql_query($sql);
		if (mysql_errno())
		{			
			$result->message = mysql_errno() . ": " . mysql_error();			
		}
		else
		{
			$result->paper_id		= $this->iPaperId;
			$res = mysql_fetch_object($query);
			$result->paper_total	= $res->total;
			$result->status			= true;
		}
		
		return $result;
	}
	
	static function UpdateDelivery($iddelivery, $param, $value) 
	{
		switch ($param) {
			case "invoice" :
			case "creation" :
			case "supplier" :
			case "payer" :
				$sql = sprintf("UPDATE press_track_new.delivery "
					 . "SET %s = '%s' WHERE iddelivery = %d", 
					 $param, $value, $iddelivery);
			break;
			
			case "sum" :
				$sql = sprintf("UPDATE press_track_new.delivery " 
					 . "SET sum = %d WHERE iddelivery = %d", $value, $iddelivery);
			break;
			
			case "num" :
				$sql = "SELECT num, paper FROM press_track_new.delivery "
					 . "WHERE iddelivery = $iddelivery";
				$query = mysql_query($sql);
				$res = mysql_fetch_object($query);
				if (mysql_errno())
				{
					return mysql_errno() . ": " . mysql_error();
				}
				
				if ($res->num != $value) 
				{
					$val = $value - $res->num; 
					$sql = "UPDATE paper SET paper.total = paper.total + $val "
						 . "WHERE paper.idpaper = $res->paper";
					$query = mysql_query($sql);
					if (mysql_errno())
					{
						return mysql_errno() . ": " . mysql_error();
					}
				}
		
				$sql = sprintf("UPDATE press_track_new.delivery SET num = %d "
					 . "WHERE iddelivery = %d", $value, $iddelivery);
			break;
			
			case "paper" :
				$sql = "SELECT num, paper FROM press_track_new.delivery "
					 . "WHERE iddelivery = $iddelivery LIMIT 1";
				$query = mysql_query($sql);
				$res = mysql_fetch_object($query);
				if (mysql_errno())
				{
					return mysql_errno() . ": " . mysql_error();
				}
				
				if ($res->paper != $value) 
				{
					$sql = "UPDATE paper SET paper.total = paper.total - $res->num " 
						 . "WHERE press_track_new.paper.idpaper = $res->paper";
					$query = mysql_query($sql);
					if (mysql_errno())
					{
						return mysql_errno() . ": " . mysql_error();
					}
					$sql = "UPDATE paper SET paper.total = paper.total + $res->num " 
						 . "WHERE press_track_new.paper.idpaper = $value";
					$query = mysql_query($sql);
					if (mysql_errno())
					{
						return mysql_errno() . ": " . mysql_error();
					}
				}
		
				$sql = sprintf("UPDATE press_track_new.delivery SET paper = %d "
					 . "WHERE iddelivery = %d", $value, $iddelivery);
			break;
		}
		
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else
		{
			return "successful";
		}
	}
	
	static function RemoveDelivery($iddelivery)
    {
		$sql = sprintf("DELETE FROM press_track_new.delivery "
			 . "WHERE iddelivery = %d", $iddelivery);
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}			
		else
		{
			return "successful";
		}			
	}
	
	static function AddIssue($paper, $num, $cost, $creation, $request, $label)
	{
		$result->status = false;
		
		$sql = sprintf("INSERT INTO press_track_new.issue (issue.paper, issue.num, "
			 . "issue.cost, issue.creation, issue.request, issue.label) "
			 . "VALUES (%d, %d, %d, '%s', %d, '%s')", 
			 $paper, $num, $cost, $creation, $request, $label);
		$query = mysql_query($sql);
		if (mysql_errno()) 
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		else
		{
			$result->idissue = mysql_insert_id();
		} 	
		
		if ($creation != "??/??/????") 
		{
			$sql = "UPDATE press_track_new.paper "
				 . "SET press_track_new.paper.total = press_track_new.paper.total - $num "
				 . "WHERE press_track_new.paper.idpaper = $paper";
			$query = mysql_query($sql);
			
			$sql = "UPDATE press_track_new.task "
				 . "SET press_track_new.task.state = 4 "
				 . "WHERE press_track_new.task.idtask = $request";
			$query = mysql_query($sql);
		}		
		
		$sql = "SELECT total FROM press_track_new.paper " 
			 . "WHERE press_track_new.paper.idpaper = $paper";
		$query = mysql_query($sql);
		if (mysql_errno())
		{			
			$result->message = mysql_errno() . ": " . mysql_error();			
		}
		else
		{
			$res = mysql_fetch_object($query);
			$result->paper_total = $res->total;
			$result->status = true;
		}			
		return $result;
	}
	
	static function UpdateIssue($idissue, 
								$paper, 
								$creation, 
								$cost, 
								$num) 
	{	
		if ($creation != "??/??/????") 
		{
			$sql = "SELECT creation, num, paper, request FROM press_track_new.issue "
				 . "WHERE idissue = $idissue";
			$query = mysql_query($sql);
			if (mysql_errno())
			{			
				return mysql_errno() . ": " . mysql_error();			
			}
			$res = mysql_fetch_object($query);
			
			if ($res->creation == "??/??/????") 
			{
				$sql = "UPDATE press_track_new.paper "
					 . "SET press_track_new.paper.total = press_track_new.paper.total - $num "
					 . "WHERE press_track_new.paper.idpaper = $paper";
				$query = mysql_query($sql);
				if (mysql_errno())
				{			
					return mysql_errno() . ": " . mysql_error();			
				}
			
				$sql = "UPDATE press_track_new.task SET press_track_new.task.state = 4 "
					 . "WHERE press_track_new.task.idtask = $res->request";
				$query = mysql_query($sql);
				if (mysql_errno())
				{			
					return mysql_errno() . ": " . mysql_error();			
				}
			} 
			else
			{			
				if ($res->paper != $paper) 
				{
					$sql = "UPDATE press_track_new.paper "
						. "SET press_track_new.paper.total = press_track_new.paper.total + $res->num "
						. "WHERE press_track_new.paper.idpaper = $res->paper";
					$query = mysql_query($sql);
					if (mysql_errno())
					{			
						return mysql_errno() . ": " . mysql_error()  . " " . $paper;			
					}
				
					$sql = "UPDATE press_track_new.paper "
						 . "SET press_track_new.paper.total = press_track_new.paper.total - $res->num "
						 . "WHERE press_track_new.paper.idpaper = $paper";
					$query = mysql_query($sql);
					if (mysql_errno())
					{			
						return mysql_errno() . ": " . mysql_error();			
					}
				}				
			}
		}
		
		$sql = sprintf("UPDATE press_track_new.issue "
			 . "SET paper = %d, num = %d, cost = %d, creation = '%s' "  
			 . "WHERE idissue = %d", $paper, $num, $cost, $creation, $idissue);
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}			
		return "successful";
	}
	
	static function UpdateIssueParam($idissue, $param, $value)
	{
		switch ($param) {
			case "creation" :
				$sql = sprintf("UPDATE press_track_new.issue "
					 . "SET %s = '%s' WHERE idissue = %d", 
					 $param, $value, $idissue);
				$query = mysql_query($sql);
			break;
			
			case "cost" :
				$sql = sprintf("UPDATE press_track_new.issue " 
					 . "SET cost = %d WHERE idissue = %d", $value, $idissue);
				$query = mysql_query($sql);
			break;
			
			case "num" :
				$sql = "SELECT num, paper FROM press_track_new.issue "
					 . "WHERE press_track_new.issue.idissue = $idissue";
				$query = mysql_query($sql);				
				if (mysql_errno())
				{			
					return mysql_errno() . ": " . mysql_error();			
				}
				$res = mysql_fetch_object($query);
				
				if ($res->num != $num) 
				{
					$val = $value - $res->num; 
					$sql = "UPDATE press_track_new.paper "
						 . "SET paper.total = paper.total - $val "
						 . "WHERE press_track_new.paper.idpaper = $res->paper";
					$query = mysql_query($sql);
					if (mysql_errno())
					{			
						return mysql_errno() . ": " . mysql_error();			
					}
				}
				
				$sql = sprintf("UPDATE press_track_new.issue " 
					 . "SET num = %d WHERE idissue = %d", $value, $idissue);
				$query = mysql_query($sql);
			break;
			
			case "paper" :
				$sql = "SELECT num, paper FROM press_track_new.issue "
					 . "WHERE press_track_new.issue.idissue = $idissue";
				$query = mysql_query($sql);				
				if (mysql_errno())
				{			
					return mysql_errno() . ": " . mysql_error() . " step 1";			
				}
				$res = mysql_fetch_object($query);
				
				$sql = "UPDATE press_track_new.paper "
					. "SET press_track_new.paper.total = press_track_new.paper.total + $res->num "
					. "WHERE press_track_new.paper.idpaper = $res->paper";
				$query = mysql_query($sql);
				if (mysql_errno())
				{			
					return mysql_errno() . ": " . mysql_error()  . " " . $paper;			
				}
				
				$sql = "UPDATE press_track_new.paper "
					 . "SET press_track_new.paper.total = press_track_new.paper.total - $res->num "
					 . "WHERE press_track_new.paper.idpaper = $value";
				$query = mysql_query($sql);
				if (mysql_errno())
				{			
					return mysql_errno() . ": " . mysql_error();			
				}	
				
				$sql = sprintf("UPDATE press_track_new.issue " 
					 . "SET paper = %d WHERE idissue = %d", $value, $idissue);
				$query = mysql_query($sql);				
			break;
		}
		
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		else
		{
			return "successful";
		}	
	}
	
	static function RemoveIssue($id)
    {
		$sql = sprintf("DELETE FROM press_track_new.issue WHERE idissue = %d", $id);
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();	
		}
		else
		{
			return "successful"; 
		}
	}
	
	/*static function GetIssue()
    {
		$sql = "SELECT * FROM press_track_new.issue";
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();	
		}
		else
		{
			return "successful"; 
		}
	}*/
	
	static function GetRequestForPaper($table, $year)
	{
		set_time_limit(0);
		switch ($table){
			case "cur_request":
				$sql = "SELECT *, name FROM press_track_new.order, press_track_new.user "
					 . "WHERE press_track_new.order.state != 4 "
					 . "AND press_track_new.order.state != 8 "
					 . "AND press_track_new.user.iduser = press_track_new.order.manager "
					 . "ORDER BY manager";
			break;
			
			case "all_request":
				$sql = "SELECT *, name FROM press_track_new.order, press_track_new.user "
					 . "WHERE LOCATE($year, press_track_new.order.creation, 6) "
					 . "AND press_track_new.user.iduser = press_track_new.order.manager "
					 . "AND press_track_new.order.state != 8 ORDER BY manager";
			break;
		}		
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		while ($order = mysql_fetch_object($query)) 
		{
			if ($table == "cur_request")
			{
				$sql = "SELECT * FROM press_track_new.task "
					 . "WHERE press_track_new.task.order = $order->idorder "
					 . "AND press_track_new.task.label = 'бумага' "
					 . "AND press_track_new.task.state IN(1,6) "
					 . "AND press_track_new.task.idtask IN(SELECT task_id FROM relation)";
			}
			else if ($table == "all_request")
			{
				$sql = "SELECT * FROM task WHERE press_track_new.task.order = $order->idorder "
					 . "AND press_track_new.task.label = 'бумага' "
					 . "AND press_track_new.task.state != 1 "
					 . "AND press_track_new.task.state != 6 "
					 . "AND press_track_new.task.idtask IN(SELECT task_id FROM relation)";
			}
			$query2 = mysql_query($sql);
			if (mysql_errno())
			{
				return mysql_errno() . ": " . mysql_error();
			}
			
			while ($paper = mysql_fetch_object($query2)) 
			{
				$order_copy 		= clone $order;
				$order_copy->paper	= $paper;
				
				$paper_parts = explode("\r", $order_copy->paper->value);
				$order_copy->paper->paper_label	= trim(str_replace("название : ",	"", $paper_parts[0]));
				$order_copy->paper->paper_thick	= trim(str_replace("плотность : ",	"", $paper_parts[1]));
				$order_copy->paper->paper_size	= trim(str_replace("формат : ", 	"", $paper_parts[2]));
				$order_copy->paper->paper_cut	= trim(str_replace("доля резки : ", "", $paper_parts[3]));
				$order_copy->paper->is_customer = trim(str_replace("заказчика : ",	"", $paper_parts[4]));
				
				$order_copy->press = null;
				$order_copy->press->press_label = "";
				$order_copy->press->press_run 	= "";
				$order_copy->press->press_waste = "";
				$order_copy->press->press_sheet = "";
				$order_copy->press->press_color = "";
				 
				$sql = sprintf("SELECT product_id as product_id FROM relation "
					 . "WHERE relation.task_id = %d LIMIT 1", $paper->idtask);
				$query_product_id = mysql_query($sql);
				if (mysql_errno())
				{
					return mysql_errno() . ": " . mysql_error();
				}
				
				while ($result = mysql_fetch_object($query_product_id)) 
				{				
					if ($order_copy->group_num > 1)
					{
						$sql = "SELECT * FROM press_track_new.product WHERE idproduct = $result->product_id";
						$query_pr = mysql_query($sql);
						if (mysql_errno())
						{
							return mysql_errno() . ": " . mysql_error();
						}
						$res_product = mysql_fetch_object($query_pr);
						$order_copy->label = $order_copy->label . '-' . $res_product->group;
					}
				
					$sql = "SELECT task_id AS task_id FROM press_track_new.relation "
						 . "WHERE product_id = $result->product_id";
					$query3 = mysql_query($sql);
					if (mysql_errno())
					{
						return $result;
					}
					while ($task = mysql_fetch_object($query3)) 
					{
						$sql = "SELECT * FROM press_track_new.task "
							 . "WHERE press_track_new.task.idtask = $task->task_id "
							 . "AND press_track_new.task.label = 'печать' LIMIT 1";
						$query4 = mysql_query($sql);
						while ($press = mysql_fetch_object($query4)) 
						{
							$order_copy->press = $press;
							$press_parts = explode("\r", $order_copy->press->value);
							$order_copy->press->press_label = trim(str_replace("машина : ", 		"", $press_parts[0]));
							$order_copy->press->press_run 	= trim(str_replace("тираж : ", 			"", $press_parts[1]));
							$order_copy->press->press_waste = trim(str_replace("на приладку : ", 	"", $press_parts[2]));
							$order_copy->press->press_sheet = trim(str_replace("формат печати : ", 	"", $press_parts[3]));
							$order_copy->press->press_color = trim(str_replace("красочность : ", 	"", $press_parts[4]));
						}
					}
					break;
				}
				
				if ($order_copy->customer != NULL && $order_copy->customer != '?')
				{
					$sql = "SELECT name as cust_label FROM press_track_new.user "
						 . "WHERE press_track_new.user.iduser = $order_copy->customer";
					$query_cust = mysql_query($sql);
					if (mysql_errno())
					{
						return mysql_errno() . ": " . mysql_error();
					}
					while ($cust = mysql_fetch_object($query_cust)) 
					{
						$order_copy->customer_label = $cust->cust_label;
					}
				}		
				$order_copy->total = 0;	
				$ar_result[] = $order_copy;
			}			
		}		
		return $ar_result;
	}
	
	static function GetSuppliers()
	{
		/*
		$sql = "SELECT DISTINCT supplier as label "
			 . "FROM press_track_new.delivery WHERE LENGTH(supplier) > 0 "
			 . "AND supplier != 'Коррекция +' ORDER BY label";
		*/
		$result[] = "Бумага заказчика";
		$result[] = "Берег";
		$result[] = "Бумоптторг";
		$result[] = "Комус";
		$result[] = "Мап Мерчант";
		$result[] = "Регент";
		return $result;
	}
	
	static function GetPayers()
	{
		$sql = "SELECT DISTINCT payer as label "
			 . "FROM press_track_new.delivery WHERE LENGTH(payer) > 0 ORDER BY label";
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			return mysql_errno() . ": " . mysql_error();
		}
		
		while ($res = mysql_fetch_object($query)) 
		{
			$result[] = $res;
		}		
		return $result;
	}
}
?>